package com.cpit.qualitycontrol.dao.jpqlquery;

import com.cpit.qualitycontrol.entity.SubTask;
import com.github.pagehelper.StringUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Component;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Query;
import javax.persistence.TypedQuery;
import java.util.Iterator;
import java.util.List;

/**
 * Created by xxx on 2017/10/1.
 */
@Component
public class SubTaskQueryRepository {

    @Autowired
    EntityManagerFactory entityManagerFactory;

    private static String strSqlFromJoin = " FROM SubTask s , DeviceType dt , " +
            "Device d , Project p , ProductLine pl , WorkOrderType wot " +
//            ", Organization o " +
            "where s.deviceId = d.id " +
            "and d.deviceTypeId = dt.id " +
            "and d.projectId = p.id " +
            "and d.productLineId = pl.id " +
//            "and s.currentOrganizationTeamId = o.id " +
            "and s.workOrderTypeId = wot.id ";


    public SubTask querySubTaskById(Long subTaskId) {
        EntityManager entityManager = entityManagerFactory.createEntityManager();

        String strJpqlSql = getSubTaskJpqlSqlFixCondition().append(" and s.subTaskId = " + subTaskId).toString();
        TypedQuery<SubTask> typedQuery = entityManager.createQuery(strJpqlSql, SubTask.class);

        return typedQuery.getSingleResult();
    }

    public Page<SubTask> querySubTask(Long productLineId, String projectName, String constructionTeamName,
                                      String deviceName, Long deviceTypeId, Long workOrderTypeId,
                                      Integer subTaskState, List<String> executionIDList, Pageable pageable,
                                      String addr) {


        EntityManager entityManager = entityManagerFactory.createEntityManager();


        //查询记录总数
        String strJpqlCountSql = getSubTaskJpqlCountSqlFixCondition().append(getSubTaskSqlPartOfCondition(productLineId,
                projectName, constructionTeamName, deviceName, deviceTypeId, workOrderTypeId, subTaskState,
                executionIDList, addr)).toString();
        Query query = entityManager.createQuery(strJpqlCountSql);
        query.setParameter("execIDs", executionIDList);
        Long recordCount = (Long) query.getSingleResult();


        String strJpqlSql = getSubTaskJpqlSqlFixCondition().append(getSubTaskSqlPartOfCondition(productLineId,
                projectName, constructionTeamName, deviceName, deviceTypeId, workOrderTypeId, subTaskState,
                executionIDList, addr))
                .append(getSqlPartOfOrderBy(pageable)).toString();
        TypedQuery<SubTask> typedQuery = entityManager.createQuery(strJpqlSql, SubTask.class);
        typedQuery.setParameter("execIDs", executionIDList);

        typedQuery.setMaxResults(pageable.getPageSize());   //计数从0开始
        typedQuery.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());

        return new PageImpl<>(typedQuery.getResultList(), pageable, recordCount);
    }


    /**
     * 生成动态可变条件部分字符串
     *
     * @param productLineId
     * @param projectName
     * @param constructionTeamName
     * @param deviceName
     * @param deviceTypeId
     * @param workOrderTypeId
     * @param subTaskState
     * @param executionIDList
     * @return
     */
    private StringBuilder getSubTaskSqlPartOfCondition(Long productLineId, String projectName, String constructionTeamName,
                                                       String deviceName, Long deviceTypeId, Long workOrderTypeId,
                                                       Integer subTaskState, List<String> executionIDList, String addr) {

        StringBuilder sbCondition = new StringBuilder("");
        if (productLineId != null && productLineId != 0) {
            sbCondition.append(" and pl.id = ").append(productLineId);
        }
        if (StringUtil.isNotEmpty(projectName)) {
            sbCondition.append(" and p.name like '%").append(projectName).append("%'");
        }
        if (StringUtil.isNotEmpty(constructionTeamName)) {
            sbCondition.append(" and s.constructionTeamNames like '%").append(constructionTeamName).append("%'");
        }
        if (StringUtil.isNotEmpty(deviceName)) {
            sbCondition.append(" and s.deviceId like '%").append(deviceName).append("%'");
        }
        if (addr != null && !addr.isEmpty()) {
            sbCondition.append(" and d.addrs like '%").append(addr).append("%'");
        }
        if (deviceTypeId != null && deviceTypeId != 0) {
            sbCondition.append(" and d.deviceTypeId = ").append(deviceTypeId);
        }
        if (workOrderTypeId != null && workOrderTypeId != 0) {
            sbCondition.append(" and s.workOrderTypeId = ").append(workOrderTypeId);
        }
        if (subTaskState != null) {
            sbCondition.append(" and s.subTaskState = ").append(subTaskState);
        }
        if (executionIDList != null && executionIDList.size() != 0) {
            sbCondition.append(" and s.actExecutionId in (:execIDs)");
        }

        return sbCondition;
    }


    /**
     * 生成含固定条件查询记录的JPQL语句
     *
     * @return JPQL语句字符串对象
     */
    private StringBuilder getSubTaskJpqlSqlFixCondition() {
//        return new StringBuilder("SELECT new SubTask(s, dt.id, dt.name, p.id, p.name, pl.name, wot.name, o.name) "
        return new StringBuilder("SELECT new SubTask(s, dt.id, dt.name, p.id, p.name, pl.name, wot.name, d.addrs) "
                + strSqlFromJoin);
    }


    /**
     * 生成含固定条件查询记录数量的JPQL语句
     *
     * @return JPQL语句字符串对象
     */
    private StringBuilder getSubTaskJpqlCountSqlFixCondition() {
        return new StringBuilder("SELECT count(s) " + strSqlFromJoin);
    }


    /**
     * 生成JPQL语句中排序部分
     *
     * @param pageable 含排序的分页对象Pageable对象
     * @return 排序部分字符串对象
     */
    private StringBuilder getSqlPartOfOrderBy(Pageable pageable) {
        final String STRING_ORDER_BY = " order by ";
        StringBuilder stringBuilder = new StringBuilder("");

        Sort sort = pageable.getSort();
        if (sort != null) {
            stringBuilder.append(STRING_ORDER_BY);
            Iterator<Sort.Order> iterator = sort.iterator();
            while (iterator.hasNext()) {
                Sort.Order order = iterator.next();
                if (order == null)
                    break;
                String orderProperty = order.getProperty();
                if ("subTaskName".equals(orderProperty))
                    stringBuilder.append("s.subTaskName");
                else if ("deviceId".equals(orderProperty))
                    stringBuilder.append("s.deviceId");
                else if ("deviceTypeId".equals(orderProperty))
                    stringBuilder.append("d.deviceTypeId");
                else if ("projectName".equals(orderProperty))
                    stringBuilder.append("p.projectName");
                else if ("workOrderTypeId".equals(orderProperty))
                    stringBuilder.append("s.workOrderTypeId");
                else if ("productLineName".equals(orderProperty))
                    stringBuilder.append("pl.productLineName");
                else if ("organizationTeamName".equals(orderProperty))
                    stringBuilder.append("s.organizationTeamName");
                else if ("subTaskState".equals(orderProperty))
                    stringBuilder.append("s.subTaskState");
                else if ("assignTaskTime".equals(orderProperty))
                    stringBuilder.append("s.assignTaskTime");
                else if ("endTaskTime".equals(orderProperty))
                    stringBuilder.append("s.endTaskTime");
                else if ("description".equals(orderProperty))
                    stringBuilder.append("s.description");
                else
                    stringBuilder.append("s.").append(orderProperty);
                stringBuilder.append(" ").append(order.getDirection().name()).append(",");
            }
        }
        if (stringBuilder.lastIndexOf(",") > 0)
            stringBuilder.setLength(stringBuilder.length() - 1);
        if (stringBuilder.length() == STRING_ORDER_BY.length())
            stringBuilder.setLength(0);

        return stringBuilder;
    }

}
